using System;
using System.Linq;
using JNPF.Utils;
using System.Threading.Tasks;
using JNPF.Data;
using JNPF.Data.Util;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using JNPF.Basics.Models;
using System.Data.Common;
using System.Text;
using System.Data.SqlTypes;
using NPOI.HSSF.Record.Chart;
using System.Reflection;

namespace JNPF.Basics.Logic
{
    public class CustomerloandetailService : DataFactory, CustomerloandetailIService
    {
        /// <summary>
        /// 获取信息
        /// </summary>
        /// <param name="id">主键id</param>
        /// <returns></returns>
        public async Task<CustomerloandetailEntity> GetInfo(int id)
        {
            using (var db = this.Basic)
            {
                return await db.GetEntityAsync<CustomerloandetailEntity>(m => m.Id == id);
            }
        }

        /// <summary>
        /// 获取信息
        /// </summary>
        /// <param name="id">主键id</param>
        /// <returns></returns>
        public async Task<CustomerloandetailEntity> GetInfoByUUID(string uuid)
        {
            using (var db = this.Basic)
            {
                return await db.GetEntityAsync<CustomerloandetailEntity>(m => m.CustomerLoanUUID == uuid);
            }
        }

        /// <summary>
        /// 获取信息
        /// </summary>
        /// <param name="id">主键id</param>
        /// <returns></returns>
        public async Task<CustomerloandetailEntity> GetInfoBySerialid(string serialid)
        {
            using (var db = this.Basic)
            {
                return await db.GetEntityAsync<CustomerloandetailEntity>(m => m.Serialid == serialid);
            }
        }
        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailEntity>> GetPageList(CustomerloandetailListQueryInput input)
        {
            using (var db = this.Basic)
            {
                var queryWhere = ExtLinq.True<CustomerloandetailEntity>();
                var queryParam = input.queryJson.ToObject();
                //查询合伙人人ID
                if (!string.IsNullOrEmpty(input.partnerUserId))
                {
                    queryWhere = queryWhere.And(m => m.PartnerUserId.Contains(input.partnerUserId));
                }
                //查询合伙人名称
                if (!string.IsNullOrEmpty(input.partnerUserRealName))
                {
                    queryWhere = queryWhere.And(m => m.PartnerUserRealName.Contains(input.partnerUserRealName));
                }

                return await db.GetIQueryable<CustomerloandetailEntity>().Where(queryWhere).GetPageResultAsync(input);
            }
        }

        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailListOutput>> GetPageListByCus(CustomerloandetailListQueryInput input)
        {
            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
	                detail.*,
	                need.F_NeedDesc,
	                need.F_NeedAmount,
	                need.F_needDate,
	                need.F_UserName userName,
	                loan.F_BankName bankName,
	                cus.F_CustomerName  customerName
                FROM
	                jiushi_customerloandetail detail
	                LEFT JOIN jiushi_Customerloan loan ON detail.F_CustomerLoanUUID = loan.F_UUID
	                LEFT JOIN jiushi_customerneed need ON loan.F_CustomerNeedId = need.F_ID
	                LEFT JOIN jiushi_customerbase cus ON cus.F_id = loan.F_CustomerId 
                WHERE
	                1 = 1");

                //查询手机号
                if (!string.IsNullOrEmpty(input.userName))
                {

                    dbSql.Append(" AND need.F_UserName like  @userName");
                    parameters.Add(DataHelper.CreateParameter("@userName", "%" + input.userName + "%"));
                }
                //查询手机号
                if (!string.IsNullOrEmpty(input.bankName))
                {
                    dbSql.Append(" AND   loan.F_BankName like  @bankName");
                    parameters.Add(DataHelper.CreateParameter("@bankName", "%" + input.bankName + "%"));
                }
                //公司名称
                if (!string.IsNullOrEmpty(input.customerName))
                {

                    dbSql.Append(" AND  cus.F_CustomerName like  @customerName");
                    parameters.Add(DataHelper.CreateParameter("@customerName", "%" + input.customerName + "%"));
                }

                //公司名称
                if (!string.IsNullOrEmpty(input.customerName))
                {

                    dbSql.Append(" AND  cus.F_CustomerName like  @customerName");
                    parameters.Add(DataHelper.CreateParameter("@customerName", "%" + input.customerName + "%"));
                }

                //合伙人名称
                if (!string.IsNullOrEmpty(input.partnerUserRealName))
                {

                    dbSql.Append(" AND  cus.F_PartnerUserRealName like  @partnerUserRealName");
                    parameters.Add(DataHelper.CreateParameter("@partnerUserRealName", "%" + input.partnerUserRealName + "%"));
                }


                //上级合伙人名称
                if (!string.IsNullOrEmpty(input.parentPartnerUserRealName))
                {

                    dbSql.Append(" AND  detail.F_PARENTPARTNERUSERREALNAME like  @parentPartnerUserRealName");
                    parameters.Add(DataHelper.CreateParameter("@parentPartnerUserRealName", "%" + input.parentPartnerUserRealName + "%"));
                }

                return await PageHelper.GetPageResultAsync<CustomerloandetailListOutput>(db, input, dbSql.ToString(), parameters.ToArray());

            }


        }



        /// <summary>
        /// 企业用户  ===  手机号码对应起来的 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailListOutput>> GetBusPageList(CustomerloandetailListQueryInput input)
        {

            UserInfo userInfo = UserProvider.Instance.Get();
            input.sidx = "F_CreatorTime";
            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
	                lodetail.F_ID,
	                lodetail.F_Serialid,
	                lodetail.F_CustomerLoanUUID,
	                lodetail.F_ParentSerialid,
	                lodetail.F_PartnerUserId,
	                lodetail.F_PartnerUserRealName,
	                lodetail.F_PartnerPoint,
	                lodetail.F_ParentPartnerUserId,
	                lodetail.F_ParentPartnerUserRealName,
	                lodetail.F_ParentUserPoints,
	                lodetail.F_Type,
	                lodetail.F_EndDate,

                    IFNULL( lodetail.F_Amount, loan.F_Amount ) F_Amount,
	                
                    lodetail.F_EndLendingAmount,
	                lodetail.F_EndLendingDesc,
	                lodetail.F_LendingDate,
	                lodetail.F_SortCode,
	                lodetail.F_PartnerPoints,
	                lodetail.F_ParentPartnerPoints,
	                lodetail.F_EnabledMark,
	                lodetail.F_CreatorTime,
	                lodetail.F_CreatorUser,
	                lodetail.F_CreatorUserId,
	                lodetail.F_Attachment,
	                lodetail.F_EndAttachment,
	                need.F_needDate,
	                need.F_NeedDesc,
                    loan.F_Id loanId,
                    buser.F_RealName userName,
	                loan.F_BankUserName,
	                loan.F_BankName,
                    IFNULL( lodetail.F_Term, loan.F_Term ) F_Term,
	                IFNULL( lodetail.F_Rate, loan.F_Rate ) F_Rate,
	                IFNULL( lodetail.F_Method, loan.F_Method ) F_Method,
	                IFNULL( lodetail.F_Detail, loan.F_Detail ) F_Detail,
                    cus.F_CustomerName,
                    cus.F_HideName
                FROM
	                jiushi_Customerloandetail lodetail
	                INNER JOIN jiushi_customerloan loan ON lodetail.F_CustomerLoanUUID = loan.F_UUID
	                INNER JOIN jiushi_customerbase cus ON cus.F_ID = loan.F_CustomerId
	                INNER JOIN jiushi_customerneed need ON need.F_CustomerId = cus.F_ID
	                INNER JOIN jiushi_customeruser cususer ON cususer.F_CustomerId = cus.F_ID 
	                inner join base_user buser  on buser.F_ID = cususer.F_UserId
                WHERE
	                1 = 1 
	                AND cususer.F_Type = 1 
	                AND lodetail.F_DeleteMark = 0");



                dbSql.Append("  and cus.F_MobilePhone = @mobilePhone");
                parameters.Add(DataHelper.CreateParameter("@mobilePhone", userInfo.UserAccount));


                if (input.needId.HasValue && input.needId > 0) 
                {
                    dbSql.Append("  and need.F_ID = @needId");
                    parameters.Add(DataHelper.CreateParameter("@needId", input.needId));
                }


                return await PageHelper.GetPageResultAsync<CustomerloandetailListOutput>(db, input, dbSql.ToString(), parameters.ToArray());
            }

        }

        /// <summary>
        /// 客户经理列表（分页）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailListOutput>> GetCuseBusList(CustomerloandetailListQueryInput input)
        {

            UserInfo userInfo = UserProvider.Instance.Get();
            input.sidx = "F_CreatorTime";
            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
	lodetail.F_ID,
	lodetail.F_Serialid,
    IFNULL( lodetail.F_Amount, loan.F_Amount ) F_Amount,
	IFNULL( lodetail.F_Term, loan.F_Term ) F_Term,
	IFNULL( lodetail.F_Rate, loan.F_Rate ) F_Rate,
	IFNULL( lodetail.F_Method, loan.F_Method ) F_Method,
	IFNULL( lodetail.F_Detail, loan.F_Detail ) F_Detail,
	lodetail.F_CustomerLoanUUID,
	lodetail.F_ParentSerialid,
	lodetail.F_PartnerUserId,
	lodetail.F_PartnerUserRealName,
	lodetail.F_PartnerPoint,
	lodetail.F_ParentPartnerUserId,
	lodetail.F_ParentPartnerUserRealName,
	lodetail.F_ParentUserPoints,
	lodetail.F_Type,
	lodetail.F_EndDate,
	lodetail.F_EndLendingAmount,
	lodetail.F_EndLendingDesc,
	lodetail.F_LendingDate,
	lodetail.F_SortCode,
	lodetail.F_PartnerPoints,
	lodetail.F_ParentPartnerPoints,
	lodetail.F_EnabledMark,
	lodetail.F_CreatorTime,
	lodetail.F_CreatorUser,
	lodetail.F_CreatorUserId,
    lodetail.F_Attachment,
	lodetail.F_EndAttachment,
	need.F_needDate,
	need.F_NeedDesc,
	loan.F_BankUserName,
	loan.F_BankName,
    loan.F_Id loanId,

    buser.F_RealName userName,
    cus.F_CustomerName,
    cus.F_HideName

FROM
	jiushi_Customerloandetail lodetail
	INNER JOIN jiushi_customerloan loan ON lodetail.F_CustomerLoanUUID = loan.F_UUID
	INNER JOIN jiushi_customerbase cus ON cus.F_ID = loan.F_CustomerId
	INNER JOIN jiushi_customerneed need ON need.F_CustomerId = cus.F_ID
	inner JOIN jiushi_customerneedrecord needrecord on needrecord.F_CustomerNeedId = need.F_ID
	INNER JOIN jiushi_customeruser cususer ON cususer.F_CustomerId = cus.F_ID 
	inner join base_user buser  on buser.F_ID = cususer.F_UserId
WHERE
	1 = 1 
	AND cususer.F_Type = 1 
	AND lodetail.F_DeleteMark = 0");
                dbSql.Append(" and loan.F_BankUserId= @BankUserId");
                dbSql.Append(" and needrecord.F_AccountManagerUserId= @BankUserId"); //记录ID 也加上去
                parameters.Add(DataHelper.CreateParameter("@BankUserId", userInfo.UserId));

                if (input.needId.HasValue && input.needId > 0)
                {
                    dbSql.Append("  and need.F_ID = @needId");
                    parameters.Add(DataHelper.CreateParameter("@needId", input.needId));
                }

                return await PageHelper.GetPageResultAsync<CustomerloandetailListOutput>(db, input, dbSql.ToString(), parameters.ToArray());
            }

        }


        /// <summary>
        /// 信贷管家   列表（分页）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailListOutput>> GetPagCuseList(CustomerloandetailListQueryInput input)
        {

            UserInfo userInfo = UserProvider.Instance.Get();
            input.sidx = "F_CreatorTime";
            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
	lodetail.F_ID,
	lodetail.F_Serialid,
	lodetail.F_CustomerLoanUUID,
	lodetail.F_ParentSerialid,
	lodetail.F_PartnerUserId,
	lodetail.F_PartnerUserRealName,
	lodetail.F_PartnerPoint,
	lodetail.F_ParentPartnerUserId,
	lodetail.F_ParentPartnerUserRealName,
	lodetail.F_ParentUserPoints,
	lodetail.F_Type,
	lodetail.F_EndDate,
	
	lodetail.F_EndLendingDesc,
	lodetail.F_LendingDate,
	lodetail.F_SortCode,
	lodetail.F_PartnerPoints,
	lodetail.F_ParentPartnerPoints,
	lodetail.F_EnabledMark,
	lodetail.F_CreatorTime,
	lodetail.F_CreatorUser,
	lodetail.F_CreatorUserId,
    lodetail.F_Attachment,
	lodetail.F_EndAttachment,
	need.F_needDate,
	need.F_NeedDesc,
    loan.F_Id loanId,
    loan.F_BankUserName,
	loan.F_BankName,


    IFNULL( lodetail.F_Amount, loan.F_Amount ) F_Amount,
	lodetail.F_EndLendingAmount,
	IFNULL( lodetail.F_Term, loan.F_Term ) F_Term,
	IFNULL( lodetail.F_Rate, loan.F_Rate ) F_Rate,
	IFNULL( lodetail.F_Method, loan.F_Method ) F_Method,
	IFNULL( lodetail.F_Detail, loan.F_Detail ) F_Detail,



	buser.F_RealName userName,
    cus.F_CustomerName,
    cus.F_HideName
FROM
	jiushi_Customerloandetail lodetail
	INNER JOIN jiushi_customerloan loan ON lodetail.F_CustomerLoanUUID = loan.F_UUID
	INNER JOIN jiushi_customerbase cus ON cus.F_ID = loan.F_CustomerId
	INNER JOIN jiushi_customerneed need ON need.F_CustomerId = cus.F_ID
	INNER JOIN jiushi_customeruser cususer ON cususer.F_CustomerId = cus.F_ID 
	inner join base_user buser  on buser.F_ID = cususer.F_UserId
WHERE
	1 = 1 
	AND cususer.F_Type = 1 
	AND lodetail.F_DeleteMark = 0
	");
                dbSql.Append(" and  cususer.F_UserId = @UserId");
                parameters.Add(DataHelper.CreateParameter("@UserId", userInfo.UserId));


                if (input.needId.HasValue && input.needId > 0)
                {
                    dbSql.Append("  and need.F_ID = @needId");
                    parameters.Add(DataHelper.CreateParameter("@needId", input.needId));
                }

                return await PageHelper.GetPageResultAsync<CustomerloandetailListOutput>(db, input, dbSql.ToString(), parameters.ToArray());
            }

        }









        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<PageResult<CustomerloandetailEntity>> GetPageListBySql(CustomerloandetailListQueryInput input)
        {

            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
	                            detail.*,
	                            cus.F_CustomerName customerName 
                            FROM
	                            jiushi_customerloandetail detail
	                            LEFT JOIN jiushi_customerloan loan ON detail.F_CustomerLoanUUID = loan.F_UUID
	                            LEFT JOIN jiushi_customerbase cus ON cus.F_ID = loan.F_CustomerId 
                            WHERE
	                            1 = 1 

                            ");


                string userId = UserProvider.Instance.Get().UserId;
                //客户经理
                if (UserProvider.Instance.Get().RoleIds.ToList().Contains("419f4b03a53a4a2f8e4f1eb1bc3ee03f"))
                {
                    dbSql.Append(" AND  loan.F_BankUserId =@bankUserId ");

                    parameters.Add(DataHelper.CreateParameter("@bankUserId", userId));

                }  //企业用户
                else if (UserProvider.Instance.Get().RoleIds.ToList().Contains("7037cc3a4a8f4e0f9784b884cd324d54"))
                {
                    //根据


                    var userInfo = await new UserService().GetInfo(UserProvider.Instance.Get().UserId);
                    if (userInfo != null)
                    {

                        var customInfo = await new CustomerbaseService().GetInfoByPhone(userInfo.MobilePhone);
                        if (customInfo != null)
                        {
                            dbSql.Append(" AND  loan.F_CustomerId =@userId ");

                            parameters.Add(DataHelper.CreateParameter("@userId", customInfo.Id));
                        }
                    }

                }
                else if (UserProvider.Instance.Get().RoleIds.ToList().Contains("4cbd3517c766476eb4751a6ab0511d53"))
                {
                    //信贷管家
                    dbSql.Append(" AND  loan.F_UserId =@userId ");

                    parameters.Add(DataHelper.CreateParameter("@userId", userId));

                }
                input.sidx = "F_CreatorTime";
                return await PageHelper.GetPageResultAsync<CustomerloandetailEntity>(db, input, dbSql.ToString(), parameters.ToArray());





            }


        }


        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="customerLoanUUID">请求参数</param>
        /// <returns></returns>
        public async Task<List<CustomerloandetailEntity>> GetList(string customerLoanUUID)
        {
            using (var db = this.Basic)
            {

                return await db.GetListAsync<CustomerloandetailEntity>(m => m.CustomerLoanUUID == customerLoanUUID && m.DeleteMark == 0);

            }
        }



        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="needDetailId">需求Id</param>
        /// <returns></returns>
        public async Task<List<CustomerloandetailListOutputNew>> GetDetialByneedDetailId(int needDetailId)
        {
            PageInput input = new PageInput();

            using (var db = this.Basic)
            {
                var parameters = new List<DbParameter>();
                var dbSql = new StringBuilder();
                dbSql.Append(@"SELECT
                            detail.F_ID,
                            cus.F_CustomerName,
                            cus.F_HideName,
                            loan.F_Amount,
                            loan.F_UserName,
                            loan.F_Term,
                            loan.F_ID loanId,
                            detail.F_Type,
                            detail.F_ID,
                            loan.F_Rate,
                            detail.F_CreatorTime,
                            loan.F_BankName,
                             loan.F_BankUserName,
                            detail.F_LendingDate,
	                        detail.F_EndDate
                    FROM
	                    jiushi_customerneedrecord recored
	                    LEFT JOIN jiushi_customerloan loan ON recored.F_ID = loan.F_CustomerNeedRecordId
	                    LEFT JOIN jiushi_customerloandetail detail ON detail.F_CustomerLoanUUID = loan.F_UUID
	                    LEFT JOIN jiushi_customerbase cus ON cus.F_ID = loan.F_CustomerId
	                    where 1=1 and recored.F_ID =" + needDetailId + " order by detail.F_CreatorTime desc");

                return await db.GetListSqlAsync<CustomerloandetailListOutputNew>(dbSql.ToString());
            }


        }










        /// <summary>
        /// 创建信息
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public async Task<int> Create(CustomerloandetailEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.InsertAsync(entity);
            }
        }


        /// <summary>
        /// 创建
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public async Task Create(CustomerloandetailEntity entity, List<PointsdetailsEntity> pointsdetailsEntities, List<UserextendEntity> userextendEntities)
        {


            using (var db = this.Basic)
            {
                await db.BeginTransactionAsync();


                await db.UpdateAsync(entity);

                await db.InsertAsync(pointsdetailsEntities);

                await db.UpdateAsync(userextendEntities);

                await db.CommitTransactionAsync();
            }

        }



        /// <summary>
        /// 创建
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public async Task Create(CustomerloandetailEntity entity, CustomerloanEntity customerloanEntity, CustomerloandetailEntity customerloandetailEntity)
        {

            using (var db = this.Basic)
            {
                await db.BeginTransactionAsync();


                await db.UpdateAsync(entity);
                await db.UpdateAsync(customerloanEntity);


                await db.InsertAsync(customerloandetailEntity);

                await db.CommitTransactionAsync();
            }

        }


        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public async Task<int> Update(CustomerloandetailEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.UpdateAsync(entity);
            }
        }

        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public async Task<int> Delete(CustomerloandetailEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.DeleteAsync(entity);
            }
        }

        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        public async Task<List<CustomerloandetailEntity>> GetList(CustomerloandetailListQueryInput input)
        {
            using (var db = this.Basic)
            {
                var queryWhere = ExtLinq.True<CustomerloandetailEntity>();
                //查询合伙人人ID
                if (!string.IsNullOrEmpty(input.partnerUserId))
                {
                    queryWhere = queryWhere.And(m => m.PartnerUserId.Contains(input.partnerUserId));
                }
                //查询合伙人名称
                if (!string.IsNullOrEmpty(input.partnerUserRealName))
                {
                    queryWhere = queryWhere.And(m => m.PartnerUserRealName.Contains(input.partnerUserRealName));
                }


                //UUID
                if (!string.IsNullOrEmpty(input.customerLoanUUID))
                {
                    queryWhere = queryWhere.And(m => m.CustomerLoanUUID.Contains(input.customerLoanUUID));
                }
                return await db.GetIQueryable<CustomerloandetailEntity>().Where(queryWhere).ToListAsync();
            }
        }


    }
}